package com.kanche.bigbaby.collection;

import android.content.Context;
import android.database.Cursor;

import com.kanche.bigbaby.collection.domain.VehicleModel;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

//import com.kkche.merchant.domain.VehicleModel;

/**
 * Created by nealmi on 6/12/14.
 */
public class VehicleModelsDBHelper extends SQLiteAssetHelper {
    private static final String DATABASE_NAME = "vehicleModels.sqlite3";
    private static final int DATABASE_VERSION = 1;

    public VehicleModelsDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        setForcedUpgrade();
    }

    public List<VehicleModel> queryBrands() {
        List<VehicleModel> brands = new ArrayList<VehicleModel>();

        String queryBrands = "SELECT DISTINCT brand FROM vehicle_models";
        Cursor cursor = getReadableDatabase().rawQuery(queryBrands, new String[]{});

        while (cursor.moveToNext()) {
            VehicleModel vm = new VehicleModel();
            vm.setBrand(cursor.getString(0));

            brands.add(vm);
        }
        cursor.close();
        Collections.sort(brands, new Comparator<VehicleModel>() {
            @Override
            public int compare(VehicleModel model, VehicleModel model2) {
                char f1 = StringUtils.getFirstLetterOfPinyin(model.getBrand());
                char f2 = StringUtils.getFirstLetterOfPinyin(model2.getBrand());
                if (f1 == f2)
                    return 0;
                else if (f1 > f2)
                    return 1;
                else
                    return -1;
            }
        });

        return brands;
    }

    public List<VehicleModel> querySeries(VehicleModel queryModel) {
        List<VehicleModel> series = new ArrayList<VehicleModel>();
        String queryMakers = "SELECT distinct(maker) FROM vehicle_models where brand=? group by series order by id ";
        Cursor makerCursor = getReadableDatabase().rawQuery(queryMakers, new String[]{queryModel.getBrand()});
        List<String> makers = new ArrayList<String>();
        while (makerCursor.moveToNext()) {
            makers.add(makerCursor.getString(0));
        }
        makerCursor.close();
        String querySeries = "SELECT series FROM vehicle_models where brand=? and maker=? group by series ";
        for (String maker : makers) {
            Cursor cursor = getReadableDatabase().rawQuery(querySeries, new String[]{queryModel.getBrand(), maker});
            while (cursor.moveToNext()) {
                VehicleModel vm = new VehicleModel();

                vm.setSeries(cursor.getString(0));
                vm.setMaker(maker);
                vm.setBrand(queryModel.getBrand());

                series.add(vm);
            }
            cursor.close();
        }

//        Collections.sort(series, new Comparator<VehicleModel>() {
//            @Override
//            public int compare(VehicleModel model, VehicleModel model2) {
//                return HashUtils.hashCompare(model.getMaker(), model2.getMaker());
//            }
//        });

        return series;
    }

    public List<VehicleModel> queryModels(VehicleModel queryModel) {
        List<VehicleModel> models = new ArrayList<VehicleModel>();
        String queryYearGroups = "SELECT yearGroup FROM vehicle_models where brand=? AND maker=? AND series=? group by yearGroup order by yearGroup DESC";
        Cursor yearGroupCursor = getReadableDatabase().rawQuery(queryYearGroups, new String[]{queryModel.getBrand(), queryModel.getMaker(), queryModel.getSeries()});
        List<String> yearGroups = new ArrayList<String>();
        while (yearGroupCursor.moveToNext()) {
            yearGroups.add(yearGroupCursor.getString(0));
        }
        yearGroupCursor.close();

        for (String yearGroup : yearGroups) {
            String queryModels = "SELECT saleName FROM vehicle_models where brand=? AND maker=? AND series=? and yearGroup=? group by saleName order by yearGroup DESC";
            Cursor cursor = getReadableDatabase().rawQuery(queryModels,
                    new String[]{queryModel.getBrand(), queryModel.getMaker(), queryModel.getSeries(), yearGroup});
            while (cursor.moveToNext()) {
                VehicleModel vm = new VehicleModel();
                vm.setSaleName(cursor.getString(0));
                vm.setSeries(queryModel.getSeries());
                vm.setMaker(queryModel.getMaker());
                vm.setBrand(queryModel.getBrand());
                vm.setYearGroup(yearGroup);
                models.add(vm);
            }
//            Collections.sort(models, new Comparator<VehicleModel>() {
//                @Override
//                public int compare(VehicleModel model, VehicleModel model2) {
//                    int year1 = Integer.parseInt(model.getYearGroup());
//                    int year2 = Integer.parseInt(model2.getYearGroup());
//                    if (year1 == year2) return 0;
//                    else if (year1 > year2) return 1;
//                    else return -1;
//                }
//            });
        }

        return models;
    }

    public VehicleModel queryVehicleModel(VehicleModel param) {
        VehicleModel model = new VehicleModel();
        String queryModel = "SELECT levelId FROM vehicle_models where brand=? AND maker=? AND series=? and yearGroup=? and saleName=? order by levelId desc limit 0,1";
        Cursor cursor = getReadableDatabase().rawQuery(queryModel, new String[]{param.getBrand(), param.getMaker(), param.getSeries(), param.getYearGroup(), param.getSaleName()});
        while (cursor.moveToNext()) {
            model.setLevelId(cursor.getString(0));
            model.setYearGroup(param.getYearGroup());
            model.setSaleName(param.getSaleName());
            model.setBrand(param.getBrand());
            model.setSeries(param.getSeries());
        }
        cursor.close();
        return model;
    }

}
